#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "

-- 根据主题的需求各创建表
-- 需求1：每天每个班级各时间段的正常出勤人数、出勤率
-- 建表
DROP TABLE if exists edu_online_ads.ads_attendance_count;
create table edu_online_ads.ads_attendance_count
(
    dt string comment '日期',
    class_id string comment '班级id',
    morning_attendance_count BIGINT comment '上午正常出勤人数',
    afternoon_attendance_count BIGINT comment '下午正常出勤人数',
    evening_attendance_count BIGINT comment '晚上正常出勤人数',
    morning_attendance_ratio DECIMAL(5,2) comment '上午出勤率',
    afternoon_attendance_ratio DECIMAL(5,2) comment '下午出勤率',
    evening_attendance_ratio DECIMAL(5,2) comment '晚上出勤率'
)comment '正常出勤统计表'
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
-- 需求2：每天各个班级各时间段的迟到人数、迟到率
DROP TABLE if exists edu_online_ads.ads_late_count;
create table edu_online_ads.ads_late_count
(
    dt string comment '日期',
    class_id string comment '班级id',
    morning_late_count BIGINT comment '上午迟到人数',
    afternoon_late_count BIGINT comment '下午迟到人数',
    evening_late_count BIGINT comment '晚上迟到人数',
    morning_late_ratio DECIMAL(5,2) comment '上午迟到率',
    afternoon_late_ratio DECIMAL(5,2) comment '下午迟到率',
    evening_late_ratio DECIMAL(5,2) comment '晚上迟到率'
)comment '迟到统计表'
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
-- 需求3：每天各个班级各时间段的请假人数、请假率
DROP TABLE if exists edu_online_ads.ads_leave_count;
create table edu_online_ads.ads_leave_count
(
    dt string comment '日期',
    class_id string comment '班级id',
    morning_leave_count BIGINT comment '上午请假人数',
    afternoon_leave_count BIGINT comment '下午请假人数',
    evening_leave_count BIGINT comment '晚上请假人数',
    morning_leave_ratio DECIMAL(5,2) comment '上午请假率',
    afternoon_leave_ratio DECIMAL(5,2) comment '下午请假率',
    evening_leave_ratio DECIMAL(5,2) comment '晚上请假率'
)comment '请假统计表'
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
-- 需求4：每天各个班级各时间段的旷课人数、旷课率
DROP TABLE if exists edu_online_ads.ads_absent_count;
create table edu_online_ads.ads_absent_count
(
    dt string comment '日期',
    class_id string comment '班级id',
    morning_absent_count BIGINT comment '上午旷课人数',
    afternoon_absent_count BIGINT comment '下午旷课人数',
    evening_absent_count BIGINT comment '晚上旷课人数',
    morning_absent_ratio DECIMAL(5,2) comment '上午旷课率',
    afternoon_absent_ratio DECIMAL(5,2) comment '下午旷课率',
    evening_absent_ratio DECIMAL(5,2) comment '晚上旷课率'
)comment '旷课统计表'
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
"
